package com.mypring.mapper;

import com.mypring.entity.Distance_AvgPrice;
import com.mypring.entity.Fang;
import com.mypring.entity.Layout_AvgPrice;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;
import java.util.Objects;

import static com.alibaba.druid.sql.parser.Token.LIKE;
import static com.alibaba.druid.sql.parser.Token.THEN;
import static net.sf.jsqlparser.expression.operators.relational.LikeExpression.KeyWord.REGEXP;
import static net.sf.jsqlparser.statement.show.ShowTablesStatement.SelectionMode.FROM;

@Mapper
@Repository
public interface FangMapper {
    @Select("SELECT\n" +
            "\tSUBSTRING_INDEX( neaPlace, '-', 1 ) AS region,\n" +
            "\tCOUNT(*) AS regionNum \n" +
            "FROM\n" +
            "\ttest \n" +
            "GROUP BY\n" +
            "\tregion \n" +
            "ORDER BY\n" +
            "\tregionNum DESC;")
    List<Fang> findRegion();

    @Select("SELECT \n" +
            "    CASE \n" +
            "        WHEN CAST(distance AS SIGNED) BETWEEN 1 AND 200 THEN '1-200'\n" +
            "        WHEN CAST(distance AS SIGNED) BETWEEN 201 AND 400 THEN '201-400'\n" +
            "        WHEN CAST(distance AS SIGNED) BETWEEN 401 AND 600 THEN '401-600'\n" +
            "        WHEN CAST(distance AS SIGNED) BETWEEN 601 AND 800 THEN '601-800'\n" +
            "        WHEN CAST(distance AS SIGNED) BETWEEN 801 AND 1000 THEN '801-1000'\n" +
            "        ELSE '1000+'\n" +
            "    END AS Distance_Range,\n" +
            "    FLOOR(AVG(CAST(neaPrice AS DECIMAL))) AS Average_Price\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        -- 使用 SUBSTRING 和 INSTR 函数来提取 \"米\" 前面的数字\n" +
            "        SUBSTRING(neaTraffic, \n" +
            "                  INSTR(neaTraffic, '约') + 1, \n" +
            "                  INSTR(neaTraffic, '米') - INSTR(neaTraffic, '约') - 1\n" +
            "        ) AS distance,\n" +
            "        -- 选择 neaPrice 列\n" +
            "        neaPrice\n" +
            "    FROM \n" +
            "        test\n" +
            "    WHERE \n" +
            "        -- 确保 neaTraffic 字段包含 \"米\"\n" +
            "        neaTraffic LIKE '%米%'\n" +
            "        AND \n" +
            "        -- 确保提取出来的部分包含数字\n" +
            "        SUBSTRING(neaTraffic, \n" +
            "                  INSTR(neaTraffic, '约') + 1, \n" +
            "                  INSTR(neaTraffic, '米') - INSTR(neaTraffic, '约') - 1\n" +
            "        ) REGEXP '^[0-9]+$'\n" +
            ") AS subquery\n" +
            "GROUP BY \n" +
            "    Distance_Range\n" +
            "ORDER BY \n" +
            "    CASE \n" +
            "        WHEN Distance_Range = '1-200' THEN 1\n" +
            "        WHEN Distance_Range = '201-400' THEN 2\n" +
            "        WHEN Distance_Range = '401-600' THEN 3\n" +
            "        WHEN Distance_Range = '601-800' THEN 4\n" +
            "        WHEN Distance_Range = '801-1000' THEN 5\n" +
            "        ELSE 6\n" +
            "    END;")

    List<Distance_AvgPrice> findDistance_AvgPrice();

    @Select("SELECT \n" +
            "    SUBSTRING_INDEX(SUBSTRING_INDEX(neaInform, '|', 2), '|', -1) AS Room_Layout,\n" +
            "    ROUND(AVG(neaPrice)) AS Average_neaPrice\n" +
            "FROM \n" +
            "    test\n" +
            "GROUP BY \n" +
            "    Room_Layout\n" +
            "HAVING \n" +
            "    COUNT(*) >= 1\n" +
            "ORDER BY \n" +
            "    Room_Layout;")
    List<Layout_AvgPrice> findLayout_AvgPrice();

}